package myDaos;

import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import model.entities.ChubMapping;
import model.entities.CountriesDist;
import model.entities.DirListUpdated;

@Stateless
public class DirListDao {
	@PersistenceContext(unitName = "UnileverProcedure")
    private EntityManager em;
	
	public EntityManager getEntityManager() {
        return em;
    }
	
	
	public List<String> getAllInterfaces(){
		List<String> clist=null;
		try {
			StringBuffer queryBuffer = new StringBuffer();
			queryBuffer.append(" SELECT DISTINCT c.interfacenme FROM ControlTableDistInfo c where 1=1");
			Query q = getEntityManager().createQuery(queryBuffer.toString());
			    clist = q.getResultList();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return clist;
	}
	public List<CountriesDist> getCountriesDistributorList(String country){
		List<CountriesDist> clist=null;
		try {
			StringBuffer queryBuffer = new StringBuffer();
			queryBuffer.append(" SELECT c FROM CountriesDist c where 1=1");
			
			if(country!=null && !country.trim().equals(""))
    	    {
				queryBuffer.append(" and c.country = :country") ;	
    	    }
			Query q = getEntityManager().createQuery(queryBuffer.toString());
			if(country!=null && !country.trim().equals("")){q.setParameter("country", country);}
			    clist = q.getResultList();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return clist;
	}
	
	
	public List<ChubMapping> getRegionCountriesList(String region){
		List<ChubMapping> clist=null;
		try {
			StringBuffer queryBuffer = new StringBuffer();
			queryBuffer.append(" SELECT distinct c FROM ChubMapping c where 1=1");
			
			if(region!=null && !region.trim().equals(""))
    	    {
				queryBuffer.append(" and c.mapRegion = :region") ;	
    	    }
			Query q = getEntityManager().createQuery(queryBuffer.toString());
			if(region!=null && !region.trim().equals("")){q.setParameter("region", region);}
			    clist = q.getResultList();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return clist;
	}
	
	public List<String> getCountriesList(String region){
		List<String> clist=null;
		try {
			StringBuffer queryBuffer = new StringBuffer();
			queryBuffer.append(" SELECT distinct c.mapCountry FROM ChubMapping c where 1=1");
			
			if(region!=null && !region.trim().equals(""))
    	    {
				queryBuffer.append(" and c.mapRegion = :region") ;	
    	    }
			Query q = getEntityManager().createQuery(queryBuffer.toString());
			if(region!=null && !region.trim().equals("")){q.setParameter("region", region);}
			    clist = q.getResultList();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return clist;
	}
   
	public List<DirListUpdated> getDirsByFilter(String fileName,String country,Date from,Date to,String region){
		List<DirListUpdated> dlist=null;
		try {
			StringBuffer queryBiffer = new StringBuffer();
			queryBiffer.append(" SELECT d FROM DirListUpdated d WHERE 1=1 "); 
		    	
			if(fileName!=null && !fileName.trim().equals(""))
    	    {
				queryBiffer.append(" and d.filename LIKE :fileName") ;	
    	    }
			
			if(country!=null && !country.trim().equals(""))
    	    {
				queryBiffer.append(" AND d.countryName = :country") ;	
    	    }

			if(from!=null)
    	    {
				queryBiffer.append("  AND trunc(d.entryDate) >= trunc(:fDate)") ;	
    	    }

			if(to!=null)
    	    {
				queryBiffer.append("  AND trunc(d.entryDate) <= trunc(:tDate)") ;	
    	    }
			
			if(from == null )
    	    {
				queryBiffer.append("  AND trunc(d.entryDate) >= trunc(sysdate)-30") ;	
    	    }
			
			if(region!=null && !region.trim().equals(""))
    	    {
				queryBiffer.append("  AND d.region = :region") ;	
    	    }
			
			//queryBiffer.append(" ORDER BY d.entryDate DESC ") ;	
			
			
			System.out.println(queryBiffer.toString());
			
			Query q = getEntityManager().createQuery(queryBiffer.toString());

			if(fileName!=null && !fileName.trim().equals("")){q.setParameter("fileName", '%'+fileName+'%');} 
			if(country!=null && !country.trim().equals("")){q.setParameter("country", country);}
			if(from!=null){q.setParameter("fDate", from);}
			if(to!=null){q.setParameter("tDate", to);}
			if(region!=null && !region.trim().equals("")){q.setParameter("region", region);}
			
			dlist = q.getResultList();
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return dlist;
	}

}
